package com.xja.dao.impl;

import com.xja.connutil.ConnUtil;
import com.xja.dao.LwdDao;
import com.xja.entity.Indent;
import com.xja.entity.Register;
import com.xja.entity.User;
import com.xja.entity.WorkPeople;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class LwdDaoImpl implements LwdDao {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    @Override
    public List<User> findAllUser() {
        Connection conn = null;
        PreparedStatement ps=null;
        Statement statement = null;
        ResultSet rs = null;
        List<User> list=new ArrayList<>();
        try {
            conn= ConnUtil.getConn();
            ps=conn.prepareStatement("select * from user");
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserName(rs.getString("user_name"));
                user.setUserPhone(rs.getString("user_phone"));
                user.setUserGender(rs.getString("user_gender"));
                user.setUserCard(rs.getString("user_card"));
                user.setUserLevel(rs.getInt("user_level"));
                user.setUserPassWord(rs.getString("user_password"));
                list.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return list;
    }
//查询用户是否存在
    @Override
    public User findUserById(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        User user=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("select * from user where user_id=?");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while (rs.next()){
                user=new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserName(rs.getString("user_name"));
                user.setUserPhone(rs.getString("user_phone"));
                user.setUserGender(rs.getString("user_gender"));
                user.setUserCard(rs.getString("user_card"));
                user.setUserLevel(rs.getInt("user_level"));
                user.setUserPassWord(rs.getString("user_password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return user;
    }

    @Override
    public Integer updateUser(User user) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("update user set user_name=?,user_phone=?, user_gender=?,user_card=?,user_level=?,user_password=? where user_id=? ");
            ps.setString(1,user.getUserName());
            ps.setString(2,user.getUserPhone());
            ps.setString(3,user.getUserGender());
            ps.setString(4,user.getUserCard());
            ps.setInt(5,user.getUserLevel());
            ps.setString(6,user.getUserPassWord());
            ps.setInt(7,user.getUserId());
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }

        return i;
    }

    @Override
    public Integer deleUser(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("delete from user where user_id=? ");
            ps.setInt(1,id);
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }

        return i;
    }
    //根据用户的id创建登记信息
    @Override
    public Register createRegisterByUserId(Integer id) {
        Register register = new Register();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("SELECT u.user_id,i.room_id ,r.room_name,i.duration FROM `user` u LEFT JOIN indent i on u.user_id=i.user_id LEFT JOIN room r on i.room_id=r.room_id WHERE u.user_id=?");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            rs.next();
            register.setUserId(rs.getInt("user_id"));
            register.setRoomId(rs.getInt("room_id"));
            register.setRoomName(rs.getString("room_name"));
            register.setRegisterDay(rs.getInt("duration"));
            Date date=new Date();
            try {
                register.setInTime(sdf.parse(sdf.format(date)));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return register;
    }

    @Override
    public Integer addRegist(Register register) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("insert into register (user_id,intime,room_id,room_name,register_day) VALUES(?,?,?,?,?)");
            ps.setInt(1,register.getUserId());
            ps.setDate(2,new java.sql.Date(register.getInTime().getTime()) );//时分秒没上传成功
            ps.setInt(3,register.getRoomId());
            ps.setString(4, register.getRoomName());
            ps.setInt(5,register.getRegisterDay());
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
//删除预定表
    @Override
    public Integer deleIndent(Register register) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("delete from indent where user_id=? and room_id=?");
            ps.setInt(1,register.getUserId());
            ps.setInt(2,register.getRoomId());
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public boolean updateRegisterAndIndent(Register register) {
        Boolean a=false;
        Connection conn=null;
        try {
            conn=ConnUtil.getConn();
            conn.setAutoCommit(false);
            addRegist(register);
            deleIndent(register);
            conn.commit();
            a=true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ConnUtil.closeConn();
            }
        }
        return a;
    }

    @Override
    public Indent findIndentById(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Indent indent=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("select * from indent where user_id=?");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while (rs.next()){
                indent=new Indent();
                indent.setIndentId(rs.getInt("indent_id"));
                indent.setUserId(id);
                indent.setRoomId(rs.getInt("room_id"));
                indent.setCheckTime(rs.getDate("check-in time"));
                indent.setDuratiom(rs.getInt("duration"));
                indent.setRemark(rs.getString("remark"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return indent;
    }

    @Override
    public List<WorkPeople> findAllWork() {
        Connection conn = null;
        PreparedStatement ps=null;
        Statement statement = null;
        ResultSet rs = null;
        List<WorkPeople> list=new ArrayList<>();
        try {
            conn= ConnUtil.getConn();
            ps=conn.prepareStatement("select * from work_people");
            rs = ps.executeQuery();
            while (rs.next()){
                WorkPeople workPeople=new WorkPeople();
                workPeople.setWorkPeopleId(rs.getInt("work_people_id"));
                workPeople.setWorkPeopleName(rs.getString("work_people_name"));
                workPeople.setWorkPeoplePhone(rs.getString("work_people_phone"));
                workPeople.setWorkPeopleGender(rs.getString("work_people_gender"));
                workPeople.setWorkPeopleCard(rs.getString("work_people_card"));
                workPeople.setWorkPeopleType(rs.getString("work_people_type"));
                workPeople.setWorkPeopleMonthSalary(rs.getInt("work_month_salary"));
                workPeople.setWorkPeopleLevel(rs.getInt("work_people_level"));
                list.add(workPeople);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return list;
    }

    @Override
    public WorkPeople findWorkById(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        WorkPeople workPeople=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("select * from work_people where work_people_id=?");
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while (rs.next()){
                workPeople=new WorkPeople();
                workPeople.setWorkPeopleId(rs.getInt("work_people_id"));
                workPeople.setWorkPeopleName(rs.getString("work_people_name"));
                workPeople.setWorkPeoplePhone(rs.getString("work_people_phone"));
                workPeople.setWorkPeopleGender(rs.getString("work_people_gender"));
                workPeople.setWorkPeopleCard(rs.getString("work_people_card"));
                workPeople.setWorkPeopleType(rs.getString("work_people_type"));
                workPeople.setWorkPeopleMonthSalary(rs.getInt("work_month_salary"));
                workPeople.setWorkPeopleLevel(rs.getInt("work_people_level"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return workPeople;
    }

    @Override
    public Integer updateWork(WorkPeople workPeople) {
        Connection conn=null;
        PreparedStatement ps=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("update work_people set work_people_name=?,work_people_phone=?, work_people_gender=?,work_people_card=?,work_people_type=?,work_month_salary=?,work_people_level=? where work_people_id=? ");
            ps.setString(1,workPeople.getWorkPeopleName());
            ps.setString(2, workPeople.getWorkPeoplePhone());
            ps.setString(3, workPeople.getWorkPeopleGender());
            ps.setString(4, workPeople.getWorkPeopleCard());
            ps.setString(5,workPeople.getWorkPeopleType());
            ps.setInt(6, workPeople.getWorkPeopleMonthSalary());
            ps.setInt(7,workPeople.getWorkPeopleLevel());
            ps.setInt(8,workPeople.getWorkPeopleId());
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }

        return i;
    }

    @Override
    public Integer addWork(WorkPeople workPeople) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("insert into work_people (work_people_name,work_people_phone,work_people_gender,work_people_card,work_people_type,work_month_salary,work_people_level) VALUES(?,?,?,?,?,?,?)");
            ps.setString(1,workPeople.getWorkPeopleName());
            ps.setString(2, workPeople.getWorkPeoplePhone());
            ps.setString(3, workPeople.getWorkPeopleGender());
            ps.setString(4, workPeople.getWorkPeopleCard());
            ps.setString(5,workPeople.getWorkPeopleType());
            ps.setInt(6, workPeople.getWorkPeopleMonthSalary());
            ps.setInt(7,workPeople.getWorkPeopleLevel());
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return i;
    }
//删除工作人员
    @Override
    public Integer deleWork(Integer id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer i=null;
        try {
            conn=ConnUtil.getConn();
            ps=conn.prepareStatement("delete from work_people where work_people_id=?");
            ps.setInt(1,id);
            i= ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            ConnUtil.closeConn();
        }
        return i;
    }
}
